package com.sfj.system.utils;

import com.sfj.system.dto.userinfo.UserInfoImportDTO;
import com.sfj.system.entity.UserInfoEntity;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ooxml.POIXMLDocument;
import org.apache.poi.poifs.filesystem.FileMagic;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.poifs.filesystem.FileMagic;
import java.io.InputStream;
import java.io.PushbackInputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @author kaka
 * @Date 2025/06/30
 */
public class FileUtil {
    public static List<UserInfoImportDTO> parseXlsx(InputStream inputStream) throws Exception {
        List<UserInfoImportDTO> list = new ArrayList<>();
        PushbackInputStream pis = new PushbackInputStream(inputStream, 8);
        Workbook workbook;

        InputStream is = FileMagic.prepareToCheckMagic(inputStream);
        FileMagic fm = FileMagic.valueOf(is);

        if (fm == FileMagic.OLE2) {
            workbook = new HSSFWorkbook(is);
        } else if (fm == FileMagic.OOXML) {
            workbook = new XSSFWorkbook(is);
        } else {
            throw new IllegalArgumentException("不是有效的 Excel 文件");
        }
        Sheet sheet = workbook.getSheetAt(0);

        // 跳过表头，从第1行开始
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row == null) continue;

            UserInfoImportDTO user = new UserInfoImportDTO();

            // 注意根据你的Excel列顺序调整index
            user.setName((String) getStringCellValue(row.getCell(0)));
            user.setIphone((String) getStringCellValue(row.getCell(1)));
            user.setPoliceNum((String) getStringCellValue(row.getCell(2)));
            user.setRole((String) getStringCellValue(row.getCell(3)));
            user.setGroup((String) getStringCellValue(row.getCell(4)));
            user.setDept((String) getStringCellValue(row.getCell(5)));

            list.add(user);
        }
        workbook.close();
        return list;
    }

    // 安全获取字符串
    private static Object getStringCellValue(Cell cell) {
        if (cell == null) return null;
        cell.setCellType(CellType.STRING);
        return cell.getStringCellValue().trim();
    }

    // 安全获取数字，可能是整数或浮点数
    private static double getNumericCellValue(Cell cell) {
        if (cell == null) return 0;
        if (cell.getCellType() == CellType.NUMERIC) {
            return cell.getNumericCellValue();
        }
        if (cell.getCellType() == CellType.STRING) {
            try {
                return Double.parseDouble(cell.getStringCellValue());
            } catch (Exception e) {
                return 0;
            }
        }
        return 0;
    }
    private static Integer getIntegerValue(Cell cell) {
        double val = getNumericCellValue(cell);
        if (val % 1 != 0) {
            throw new IllegalArgumentException("字段必须是整数，但发现小数：" + val);
        }
        return (int) val;
    }
}
